#!/usr/bin/python2.6
# -*- coding: utf-8 -*-

import urllib2
import html5lib
from html5lib import treebuilders
from BeautifulSoup import BeautifulSoup
import os
import re
#from BeautifulSoup import BeautifulSoup
#import sys
import codecs
import MySQLdb
#sys.setdefaultencoding("UTF8")
#streamWriter = codecs.lookup('utf-8')[-1]
#sys.stdout = streamWriter(sys.stdout)

#sys.setdefaultencoding()
parser = html5lib.HTMLParser(tree=treebuilders.getTreeBuilder("beautifulsoup"))
soup = parser.parse(urllib2.urlopen("http://a-el.ru/catalog.htm").read())

#soup = parser.parse(unicode(open("catalog.htm").read(), 'cp1251'))
table_soup = soup.table.tbody.tr.td.table.tbody.tr.nextSibling.nextSibling.tbody
#print soup.contents[0].name


trs = table_soup.findAll(name = "tr")
th_find_re = re.compile ('\<th.*\>(?P<header>.*?)\</th\>')
td_find_re = re.compile ('\<td.*\>\s?(?P<header>.*?)\s?\</td\>')
tr_re = re.compile('\<tr\>(?P<header>.*)\</tr\>')
i=2
#j=4
category_id = 10
product_id = 100
#db= MySQLdb.connect(host='domen.ru', user='root', passwd='password', db='test2')
db= MySQLdb.connect(host='213.252.92.10', user='root', passwd='oS0eegeL', db='test4')
db.set_character_set('utf8')
cursor= db.cursor()
#cursor.execute("SELECT * FROM `jos_vm_product`")
for tr in trs:
	#if trs[i]
	#td_split = td_split_re.split(str(trs[i]))
	#for split in td_split:
	#    print split
	if th_find_re.search(str(trs[i])) != None:
#        print "***************"
#        print th_find_re.search(str(trs[i])).group('header')
#        print "***************"
		category_name = th_find_re.search(str(trs[i])).group('header')
		category_id=category_id+1
		#Создаем новую категорию
		print category_name + "  " + str(category_id)
		cursor.execute("INSERT INTO `jos_vm_category` (`category_id`, `vendor_id`, `category_name`, `category_description`, `category_thumb_image`, `category_full_image`, `category_publish`, `cdate`, `mdate`, `category_browsepage`, `products_per_row`, `category_flypage`, `list_order`) VALUES("+str(category_id)+", 1, '"+category_name+"', '"+category_name+"', '', '', 'Y', 1258969273, 1258969293, 'managed', 1, 'flypage.tpl', 1)")
		cursor.execute("INSERT INTO `jos_vm_category_xref` (`category_parent_id`, `category_child_id`, `category_list`) VALUES(0, +"+str(category_id)+", NULL)")
	else:
#        other_soup =  parser.parse(unicode(str(trs[i]),'utf-8'))
		other_soup = BeautifulSoup(''.join(unicode(str(trs[i]),'utf-8')))
		product = ""
		small_img = ""
		big_img = ""
		try:
			print other_soup.tr.td.a.img['alt']
			print other_soup.tr.td.a.img['src']
			print other_soup.tr.td.a['href']
			product = str(other_soup.tr.td.a.img['alt']) #Модель продукта например CT-100
			small_img = str(other_soup.tr.td.a.img['src']) #картинка предпросмотра
			big_img = str(other_soup.tr.td.a['href']) #большая картинка
		except:
			product = "" #Левая часть продукта
			small_img = "" #картинка предпросмотра
			big_img = "" #большая картинка
#    	        print "Нет изображения"
		try:
			print str(other_soup.tr.td.nextSibling.nextSibling.string)
#            product = str(other_soup.tr.td.nextSibling.nextSibling.string) + " - " + product #правая часть продукта
			small_text = str(other_soup.tr.td.nextSibling.nextSibling.string)  # Краткое описание продукта
		except:
			break
		if other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string != None:
			text = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string)
			print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string).encode("utf-8")
		else:
			print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling).encode("utf-8")
			text = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling)
		try:
			print other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling
			vendor = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling) #Производитель(нужно доделать запрос)
		except:
			print ("vendor error")
		try:
			print td_find_re.search(str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling)).group('header')
			price = td_find_re.search(str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling)).group('header')
		except AttributeError:
#			print td_find_re.search(str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling)).group('header')
#			price = td_find_re.search(str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling)).group('header')			
#	except:
			print ("price error")
			price = "00"
		product_id = product_id + 1
	#Добавление продукта
		cursor.execute("INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES ("+str(product_id)+", 1, 0, '"+str(product_id)+"', '"+small_text+"','"+MySQLdb.escape_string(text)+"', 'http://a-el.ru/"+small_img+"', 'http://a-el.ru/"+big_img+"', 'Y', 0.0000, 'шт.', 0.0000, 0.0000, 0.0000, 'шт.', '', 0, 1258934400, '', 'N', 0, NULL, 1258969371, 1258969741, '"+product+"', 0, '', '', 0, 'шт.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0')")
	#Добавление продукта в категорию созданную ранее
		cursor.execute("INSERT INTO `jos_vm_product_category_xref` (`category_id` ,`product_id` ,`product_list`) VALUES ('"+str(category_id)+"', '"+str(product_id)+"', '1')") 
	#Добавление цены для текущего продукта
		cursor.execute("INSERT INTO `jos_vm_product_price` (`product_price_id`, `product_id`, `product_price`, `product_currency`, `product_price_vdate`, `product_price_edate`, `cdate`, `mdate`, `shopper_group_id`, `price_quantity_start`, `price_quantity_end`) VALUES("+str(product_id)+", "+str(product_id)+", "+price+", 'RUB', 0, 0, 1258969371, 1258969741, 5, 0, 0)")
	i = i + 1

